#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
-- 需求1：每天/每月/每年线上线下以及新老学员的意向用户个数
DROP TABLE IF EXISTS edu_online_ads.ads_time_line_member_num;
CREATE TABLE edu_online_ads.ads_time_line_member_num(
    year                STRING COMMENT '年',
    month               STRING COMMENT '月',
    day                 STRING COMMENT '日',
    online_offline      STRING COMMENT '线上线下',
    new_old_member      STRING COMMENT '新老学员',
    relation_num        INT COMMENT '用户意向个数'

)COMMENT '时间意向统计表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE ;


-- 需求2：每天/每月/每年各地区的线上线下以及新老学员的意向用户个数
DROP TABLE IF EXISTS edu_online_ads.ads_area_tlm_num;
CREATE TABLE edu_online_ads.ads_area_tlm_num(
    year                STRING COMMENT '年',
    month               STRING COMMENT '月',
    day                 STRING COMMENT '日',
    area                STRING COMMENT '地区',
    online_offline      STRING COMMENT '线上线下',
    new_old_member      STRING COMMENT '新老学员',
    relation_num        INT COMMENT '用户意向个数'

)COMMENT '地区意向统计表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE ;


-- 需求3：每天/每月/每年各学科线上线下以及新老学员的意向用户个数Top10
DROP TABLE IF EXISTS edu_online_ads.ads_subject_tlm_num;
CREATE TABLE edu_online_ads.ads_subject_tlm_num(
    year                STRING COMMENT '年',
    month               STRING COMMENT '月',
    day                 STRING COMMENT '日',
    subject             STRING COMMENT '学科',
    online_offline      STRING COMMENT '线上线下',
    new_old_member      STRING COMMENT '新老学员',
    relation_num        INT COMMENT '用户意向个数'

)COMMENT '学科意向统计表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE ;


-- 需求4：每天/每月/每年各校区线上线下以及新老学员的意向用户个数Top10
DROP TABLE IF EXISTS edu_online_ads.ads_school_tlm_num;
CREATE TABLE edu_online_ads.ads_school_tlm_num(
    year                STRING COMMENT '年',
    month               STRING COMMENT '月',
    day                 STRING COMMENT '日',
    school              STRING COMMENT '校区',
    online_offline      STRING COMMENT '线上线下',
    new_old_member      STRING COMMENT '新老学员',
    relation_num        INT COMMENT '用户意向个数'

)COMMENT '校区意向统计表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE ;


-- 需求5：每天/每月/每年各来源渠道线上线下以及新老学员的意向用户个数
DROP TABLE IF EXISTS edu_online_ads.ads_channel_tlm_num;
CREATE TABLE edu_online_ads.ads_channel_tlm_num(
    year                STRING COMMENT '年',
    month               STRING COMMENT '月',
    day                 STRING COMMENT '日',
    origin_channel      STRING COMMENT '来源渠道',
    online_offline      STRING COMMENT '线上线下',
    new_old_member      STRING COMMENT '新老学员',
    relation_num        INT COMMENT '用户意向个数'

)COMMENT '来源渠道意向统计表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE ;


-- 需求6：每天/每月/每年各咨询中心线上线下以及新老学员的意向用户个数
DROP TABLE IF EXISTS edu_online_ads.ads_reference_tlm_num;
CREATE TABLE edu_online_ads.ads_reference_tlm_num(
    year                STRING COMMENT '年',
    month               STRING COMMENT '月',
    day                 STRING COMMENT '日',
    reference_center    STRING COMMENT '咨询中心',
    online_offline      STRING COMMENT '线上线下',
    new_old_member      STRING COMMENT '新老学员',
    relation_num        INT COMMENT '用户意向个数'

)COMMENT '咨询中心意向统计表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE ;


-- 需求7：每天线上线下及新老学员的有效线索个数
DROP TABLE IF EXISTS edu_online_ads.ads_day_line_member_count;
CREATE TABLE edu_online_ads.ads_day_line_member_count(
    day                 STRING COMMENT '日',
    online_offline      STRING COMMENT '线上线下',
    new_old_member      STRING COMMENT '新老学员',
    valid_clue_num      INT COMMENT '有效线索数'

)COMMENT '每天线索统计表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE ;


-- 需求8：每小时线上线下及新老学员的有效线索转化率 = 有效线索个数 / 总线索个数
DROP TABLE IF EXISTS edu_online_ads.ads_hour_line_member_rate;
CREATE TABLE edu_online_ads.ads_hour_line_member_rate(
    day                 STRING COMMENT '天',
    hour                STRING COMMENT '小时',
    online_offline      STRING COMMENT '线上线下',
    new_old_member      STRING COMMENT '新老学员',
    valid_clue_rate     DECIMAL(5,2) COMMENT '有效线索转化率'

)COMMENT '每小时线索比率表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE ;
"